package com.zhxh.nzpos.dao;

import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.zhxh.nzpos.db.DBHelper;
import com.zhxh.nzpos.model.Stock;

/**
 * Stock DAO
 * 
 * @author Administrator
 * 
 */
public class StockDao {

	DBHelper dbHelper;

	private final static String LOG_TAG = "StockDao";
	protected static final String TBL_NAME = "stock";

	public StockDao(Context context) {
		dbHelper = new DBHelper(context);
	}

	/**
	 * add new stock
	 * 
	 * @param stock
	 */
	public String save(Stock stock) {
		Log.i(LOG_TAG, "StockDao  save " + stock.toString());
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"insert into stock(ID,GoodID,GoodsName,Count,Stockdate,Life,created,modified) values(?,?,?,?,?,?,?,?)",
					new Object[] { stock.getId(), stock.getGoodId(), stock.getGoodsName(),
							stock.getCount(), stock.getStockDate(),
							stock.getLife(), stock.getCreated(),
							stock.getModified() });

			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("StockDao  save", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
		return stock.getId();
	}

	/**
	 * update a stock
	 * 
	 * @param stock
	 */

	public void update(Stock stock) {
		Log.i(LOG_TAG, "StockDao  update " + stock.toString());
		stock.setModified(System.currentTimeMillis());

		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			db.execSQL(
					"update stock set GoodID=?,GoodsName=?,Count=?,Stockdate=?,Life=?,modified=? where ID=?",
					new Object[] { stock.getGoodId(),  stock.getGoodsName(),stock.getCount(),
							stock.getStockDate(), stock.getLife(),
							stock.getModified(), stock.getId() });

			// db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("StockDao  update", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
	}
	
	/**
	 * update a stock
	 * 
	 * @param stock
	 */
	
	public void sellGoods(String goodsId, int count) {
		Log.i(LOG_TAG, "StockDao  sell goods: " + goodsId);
		long modified = System.currentTimeMillis();
		Stock stock = new Stock();
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.beginTransaction();// 开始事务
		try {
			Cursor cursor = db
					.rawQuery(
							"select ID,GoodID,Count,Stockdate,modified from stock where GoodID=? order by Stockdate desc",
							new String[] { goodsId });
			if (cursor.moveToNext()) {
				stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
				stock.setGoodId(cursor.getString(1));
				stock.setCount(cursor.getInt(2));
				stock.setStockDate(cursor.getString(3));
				stock.setModified(cursor.getLong(4));
			}
			if (stock.getCount() > count) {
				db.execSQL(
						"update stock set Count=?,modified=? where ID=?",
						new Object[] { stock.getCount() - count,modified });
			}
			cursor.close();
			
			// db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.i("StockDao  update", e.getMessage());
		}
		db.endTransaction();// 事务结束;
		db.close();
	}

	/**
	 * get a stock by id
	 * 
	 * @param i
	 * @return
	 */
	public Stock find(String id) {
		Log.i(LOG_TAG, "StockDao  find " + id);
		Stock stock = new Stock();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,GoodsName,Count,Stockdate,Life,created,modified from stock where ID=?",
						new String[] { id });
		if (cursor.moveToNext()) {
			stock = new Stock();

			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stock.setLife(cursor.getString(5));
			stock.setCreated(cursor.getLong(6));
			stock.setModified(cursor.getLong(7));
			
		}
		cursor.close();
		db.close();
		return stock;
	}

	/**
	 * get all stocks by good id and date
	 * 
	 * 返回库存信息
	 * @param i
	 * @return
	 */
	public ArrayList<Stock> findByGoodIdAndDate(String goodId, String from, String end) {
		Log.i(LOG_TAG, "StockDao  find by goodId:" + goodId);
		ArrayList<Stock> stocks = new ArrayList<Stock>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select stock.ID,stock.GoodID,stock.GoodsName,stock.Count,stock.Stockdate,goods.Price1 from stock,goods where stock.GoodID=goods.ID and goods.ID=? and stock.count>0 and stock.Stockdate>=? and stock.Stockdate<=? order by stock.modified desc",
						new String[] { goodId, from, end });
		while (cursor.moveToNext()) {
			Stock stock = new Stock();
			
			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stock.setPrice(cursor.getFloat(5));
			stocks.add(stock);
		}
		cursor.close();
		db.close();
		return stocks;
	}
	
	/**
	 * get all stocks by date
	 * 
	 * 返回库存信息
	 * @param i
	 * @return
	 */
	public ArrayList<Stock> findByDate(String from, String end) {
		Log.i(LOG_TAG, "StockDao  find all by date between:"+from+" to "+end);
		ArrayList<Stock> stocks = new ArrayList<Stock>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select stock.ID,stock.GoodID,stock.GoodsName,stock.Count,stock.Stockdate,goods.Price1 from stock,goods where stock.GoodID=goods.ID and stock.Count>0 and stock.Stockdate>=? and stock.Stockdate<=? order by stock.modified desc",
						new String[] {from, end });
		while (cursor.moveToNext()) {
			Stock stock = new Stock();
			
			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stock.setPrice(cursor.getFloat(5));
			stocks.add(stock);
		}
		cursor.close();
		db.close();
		return stocks;
	}
	
	/**
	 * get all stocks by goods type
	 * 
	 * 返回库存信息
	 * @param i
	 * @return
	 */
	public ArrayList<Stock> findByTypeId(String typeId) {
		Log.i(LOG_TAG, "StockDao  find all by goods type:"+typeId);
		ArrayList<Stock> stocks = new ArrayList<Stock>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select stock.ID,stock.GoodID,stock.GoodsName,stock.Count,stock.Stockdate from stock,goods where stock.GoodID=goods.ID and goods.CategoryID=? order by stock.modified desc",
						new String[] {typeId });
		while (cursor.moveToNext()) {
			Stock stock = new Stock();
			
			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stocks.add(stock);
		}
		cursor.close();
		db.close();
		return stocks;
	}
	
	/**
	 * delete a stock by id
	 * 
	 * @param id
	 */
	public void delete(String id) {
		Log.i(LOG_TAG, "StockDao  delete " + id);
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete from stock where ID=?", new Object[] { id });
		db.close();
	}

	/**
	 * delete all
	 * 
	 * @param id
	 */
	public void deleteAll() {
		Log.i(LOG_TAG, "  delete all from  stock: ");
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.execSQL("delete  from stock");
		db.close();
	}

	/**
	 * fetch stocks
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public List<Stock> getScrollData(int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "StockDao  getScrollData " + pageNumber + "," + pageSize);

		List<Stock> stocks = new ArrayList<Stock>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select stock.ID,stock.GoodID,stock.GoodsName,stock.Count,stock.Stockdate,goods.Price1 from stock,goods where stock.GoodID=goods.ID and stock.Count>0 order by stock.modified desc limit ?,?",
						new String[] {
								String.valueOf((pageNumber - 1) * pageSize),
								String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Stock stock = new Stock();
			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stock.setPrice(cursor.getFloat(5));
			stocks.add(stock);
		}
		cursor.close();
		db.close();
		return stocks;
	}

	/**
	 * fetch all stocks
	 * 
	 * @return
	 */
	public List<Stock> getAll() {

		Log.i(LOG_TAG, "StockDao  getScrollData ");

		List<Stock> stocks = new ArrayList<Stock>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select ID,GoodID,GoodsName,Count,Stockdate,Life,created,modified from stock where Count>0 order by modified desc",
						null);// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Stock stock = new Stock();
			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stock.setLife(cursor.getString(5));
			stock.setCreated(cursor.getLong(6));
			stock.setModified(cursor.getLong(7));
			stocks.add(stock);
		}
		cursor.close();
		db.close();
		return stocks;
	}

	/**
	 * get stock's count
	 * 
	 * @return
	 */
	public long getCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		long count = DatabaseUtils.queryNumEntries(db, TBL_NAME);
		db.close();
		return count;
	}

	/**
	 * 获取有效库存个数
	 * 
	 * @return
	 */
	public long getStockCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db
				.rawQuery(
						"select count(*) from stock where Count>0",
						null);
		long count = 0;
		if (cursor.moveToNext()) {
			count = cursor.getLong(0);
		}
		cursor.close();
		db.close();
		return count;
	}
	
	public int getCount(String cateId, String identifier, long btime, long etime) {
		Log.i(LOG_TAG, "StockDao  getScrollData " + cateId + "," + identifier);

		int count = 0;
		String sql = "select  count(stock.ID) from stock,goods where stock.Count>0 and stock.GoodID=goods.ID ";
		if (identifier.length() != 0) {
			sql += " and stock.GoodID=" + identifier;
		} 
		
			if (cateId != null) {
				sql += " and goods.CategoryID=" + cateId;
			}
			sql += " and stock.created >=" + btime + " and stock.created <=" + etime;
		

		Log.i("sql= ", sql);
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);// pageNumber页码
        if(cursor.moveToNext()){
        	count = cursor.getInt(0);
        }
		
		cursor.close();
		db.close();
		return count;

	}

	/**
	 * fetch stock
	 * 
	 * @param pageNumber
	 * @param pageSize
	 * @return
	 */
	public ArrayList<Stock> getScrollData(String cateId, String identifier,
			long btime, long etime, int pageNumber, int pageSize) {

		Log.i(LOG_TAG, "SelloutDao  getScrollData " + pageNumber + "," + pageSize);

		String sql = "select stock.ID,stock.GoodID,goods.Name,stock.Count,stock.Stockdate,stock.Life,stock.created,stock.modified from stock,goods where stock.Count>0 and stock.GoodID=goods.ID ";
		if (identifier.length() != 0) {
			sql += " and stock.GoodID=" + identifier;
		} 
		
			if (cateId != null) {
				sql += " and goods.CategoryID=" + cateId;
			}
			sql += " and stock.created >=" + btime + " and stock.created <=" + etime;
		

		sql += "  order by stock.modified desc limit ?,?";

		Log.i("sql= ", sql);
		ArrayList<Stock> stocks = new ArrayList<Stock>();
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.rawQuery(sql,
				new String[] { String.valueOf((pageNumber - 1) * pageSize),
						String.valueOf(pageSize) });// pageNumber页码
		while (cursor.moveToNext()) {// pageSize每页获取的记录数
			Stock stock = new Stock();
			stock.setId(cursor.getString(cursor.getColumnIndex("ID")));
			stock.setGoodId(cursor.getString(1));
			stock.setGoodsName(cursor.getString(2));
			stock.setCount(cursor.getInt(3));
			stock.setStockDate(cursor.getString(4));
			stock.setLife(cursor.getString(5));
			stock.setCreated(cursor.getLong(6));
			stock.setModified(cursor.getLong(7));
			stocks.add(stock);
		}
		cursor.close();
		db.close();
		return stocks;
	}
}
